const express = require('express');
const routes = express.Router();
const mysql = require('mysql');
let pool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'root',
    password : 'root',
    database:'news'
})
routes.get('/login' , (req , resp)=>{
    resp.render('login')
})
//首页查看跟模糊查询
routes.get('/' , (req , resp)=>{
    if(req.query.search){
        var sql = "select * from list where cengar like '%"+req.query.search+"%'";
    }else{
        var sql = 'SELECT * from list';
    }

    // console.log(sql)
    pool.getConnection(function (err , connection){
        if(err) throw new err;
        connection.query(sql , function (error , result){
            connection.release();
            resp.render('index' , {

                result : result
            })
            if(error) throw error;
        })
    })


})
//新闻添加页
routes.route('/add')
    .get((req , resp)=>{
        resp.render('add');
    })
    .post((req , resp)=>{
        if(req.body.title=='' ||req.body.cengar==''){
            throw new Error('有数据未提交');
        }
        let cengar = req.body.cengar;
        let title = req.body.title;
        let author = req.body.author;
        let url = req.body.url;
        let texts = req.body.texts;
        let content = req.body.content;
        let time =new Date().getTime();
        let top = req.body.top;
        let status = req.body.status;
      let sql = "insert into list (cengar , title , author , url , texts , content ,time , top , status) VALUES " +
          "('"+cengar+"', '"+title+"','"+author+"','"+url+"','"+texts+"' , '"+content+"' , '"+time+"' , '"+top+"' , '"+status+"')";
        // console.log(sql)
      pool.getConnection(function (err , connection){
          if(err) throw new err;
          connection.query(sql , function (error , result){
              connection.release();
              resp.redirect('/')
              if(error) throw  error
          })
      })
    })
routes.get('/del/:id' , (req ,resp)=>{
    let sql = 'delete from list where id='+req.params.id;
    pool.getConnection(function (err , connection){
        if(err) throw new err;
        connection.query(sql , function (error , result){
            connection.release();
           resp.json({status : 1});
            if(error) throw  error
        })
    })
})

routes.route('/edit/:id')
    .get((req , resp)=>{
        let id = req.params.id;
        let sql = 'select * from list where id='+id;

        pool.getConnection(function (err , connection){
            if(err) throw new err;
            connection.query(sql , function (error , result){
                connection.release();
                resp.render('edit' , {
                    result : result,

                });

                if(error) throw error;
            })
        })
    })
    .post((req , resp)=>{
        let id = req.params.id;
        let cengar = req.body.cengar;
        let title = req.body.title;
        let author = req.body.author;
        let url = req.body.url;
        let texts = req.body.texts;
        let content = req.body.content;
        // let time =new Date().getTime();
        let top = req.body.top;
        let status = req.body.status;
        let sql = "update list set cengar='"+cengar+"',title='"+title+"',author='"+author+"',url='"+url+"',texts='"+texts+"',content='"+content+"',top='"+top+"',status='"+status+"' where id="+id;
        pool.getConnection(function (err , connection){
            if(err) throw new err;
            connection.query(sql , function (error , result){
                connection.release();
                if(result) resp.redirect('/');
                if(error) throw error;
            })
        })
    })
routes.get('/edit' , (req , resp)=>{
    resp.render('edit')
})
routes.get('/admin' , (req, resp)=>{
    let sql = "SELECT * FROM ad";

    pool.getConnection(function (err , connection){
        if(err) throw new err;
        connection.query(sql , function (error , result){
            connection.release();
            resp.render('admin' , {

                result : result
            })
            if(error) throw error;
        })
    })
    // resp.render('admin')
})
routes.route('/admin_add')
    .get((req , resp)=>{
        resp.render('admin_add');
    })
    .post((req , resp)=>{
        let adname = req.body.adname;
        let adnum = req.body.adnum;
        let acount = req.body.acount;
        let admins = req.body.admins;
        let stat = req.body.stat;
        let sql="insert into ad (adname , adnum , acount, admins , stat) VALUES ('"+adname+"' , '"+adnum+"','"+acount+"' , '"+admins+"' ,"+stat+");"

        pool.getConnection(function (err , connection){
            if(err) throw new err;
            connection.query(sql , function (error , result){
                connection.release();
                resp.redirect('/admin')
                if(error) throw  error
            })
        })
    })
routes.route('/admin_edit/:id')
    .get((req , resp)=>{
        let id = req.params.id;
        let sql = 'select * from ad where id='+id;

        pool.getConnection(function (err , connection){
            if(err) throw new err;
            connection.query(sql , function (error , result){
                connection.release();
                resp.render('admin_edit' , {
                    result : result,

                });

                if(error) throw error;
            })
        })
    })
    .post((req , resp)=>{
        let id = req.params.id;
        let adname = req.body.adname;
        let adnum = req.body.adnum;
        let acount = req.body.acount;
        let admins = req.body.admins;
        let stat = req.body.stat;
        let sql = "update ad set adname='"+adname+"',adnum='"+adnum+"',acount='"+acount+"',admins='"+admins+"',stat='"+stat+"' where id="+id;
        pool.getConnection(function (err , connection){
            if(err) throw new err;
            connection.query(sql , function (error , result){
                connection.release();
                if(result) resp.redirect('/admin');
                if(error) throw error;
            })
        })
    })
routes.get('/admin_edit' , (req , resp)=>{
    resp.render('admin_edit')
})
routes.get('/dele/:id' , (req ,resp)=>{
    let sql = 'delete from ad where id='+req.params.id;
    pool.getConnection(function (err , connection){
        if(err) throw new err;
        connection.query(sql , function (error , result){
            connection.release();
            resp.json({status : 1});
            if(error) throw  error
        })
    })
})
module.exports = routes;